﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Ext.Net;
using System.Data;

public partial class DynamicReport : ExtBaseRpt
{
    DataSet ResultDate = null;
    protected void Page_Load(object sender, EventArgs e)
    {
        mid = Request.Params["mid"];
        var title = mydb.DM_GetExecuteScalarObj("select modulename from sys_modules where mid=" + mid).ToString();
        GridPanel1.Title = title;
        this.Title = title;
        if (!X.IsAjaxRequest)
        {
            InsActLog(mid, "浏览");
            //加载表头 查找网格
            //this.GridPanel1.ColumnModel.Add(BuildColumns(mid, Session["UserID"].ToString(), "GridPanel1", null));
            //BtnFind("","");
            //加载条件控件
            BuildCtrls("param",this.ColumPanel);
            //加载输出项
            //ShowFiledWindows();
            //加载报表网格

        }
    }

    #region 创建条件控件
    private void BuildCtrls(string name,FormPanel pn)
    {
        string sql = @"select mu_id,lindx,fieldname,fielddisplay,mu_visible,fieldwidth,datatype,gridfoot,showtype,fieldreadonly,isctrl,
                    datatype,showtype,ctrltype,ctrl_x,ctrl_y,iskeyid,ismust,sourceid,showfld,remark
                    from sys_moduleslayout a 
                    inner join sys_modulesuserlayout b on b.fid =lid 
                    inner join sys_modules m on m.mid = a.mid 
                    where m.parentid = " + mid + " and m.modulecode='" + name + "'"
                 + " and userid=" + Session["UserID"] + " order by lindx";
        DataSet ds = mydb.DM_GetDateSet(sql);
   
        if (ds != null)
        {
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                if (dr["isctrl"].ToString().ToLower() == "true")
                {
                    //文本型
                    if (dr["ctrltype"].ToString() == "0")
                    {
                        TextField text = new TextField();
                        text.ID = "txt" + dr["fieldname"].ToString();
                        text.Name = dr["fielddisplay"].ToString();
                        text.FieldLabel = dr["fielddisplay"].ToString();
                        text.Padding = 3;
                        text.LabelWidth = 80;
                        if (dr["fieldname"].ToString().ToLower().Equals("userid"))
                        {
                            text.Text = Session["UserID"].ToString();
                        }
                        else
                        {
                            text.Text = dr["remark"].ToString();
                        }
                        text.ReadOnly = (dr["fieldreadonly"].ToString() == "True");
                        if (text.ReadOnly) text.ReadOnlyCls = "readonly";
                        if (dr["ismust"].ToString() == "True")
                            text.AllowBlank = false;
                        //text.Width = Convert.ToInt32(dr["fieldwidth"]);
                        pn.Items.Add(text);
                    }
                    // 日期型
                    else if (dr["ctrltype"].ToString() == "1")
                    {
                        DateField text = new DateField();
                        text.ID = "txt" + dr["fieldname"].ToString();
                        text.Name = dr["fielddisplay"].ToString();
                        text.FieldLabel = dr["fielddisplay"].ToString();
                        //text.Width = Convert.ToInt32(dr["fieldwidth"]);
                        text.Padding = 3;
                        text.LabelWidth = 80;
                        text.Text = dr["remark"].ToString();
                        text.ReadOnly = (dr["fieldreadonly"].ToString() == "True");
                        if (text.ReadOnly) text.ReadOnlyCls = "readonly";
                        if (dr["ismust"].ToString() == "True")
                            text.AllowBlank = false;
                        //text.Format="yyyy-MM-dd";
                        pn.Items.Add(text);
                    }
                    // 备注
                    else if (dr["ctrltype"].ToString() == "2")
                    {
                        TextArea text = new TextArea();
                        text.ID = "txt" + dr["fieldname"].ToString();
                        text.Name = dr["fielddisplay"].ToString();
                        text.FieldLabel = dr["fielddisplay"].ToString();
                        //text.Width = Convert.ToInt32(dr["fieldwidth"]);
                        text.Padding = 3;
                        text.LabelWidth = 80;
                        text.Text = dr["remark"].ToString();
                        //text.Format="yyyy-MM-dd";
                        text.ReadOnly = (dr["fieldreadonly"].ToString() == "True");
                        if (text.ReadOnly) text.ReadOnlyCls = "readonly";
                        if (dr["ismust"].ToString() == "True")
                            text.AllowBlank = false;
                        pn.Items.Add(text);
                    }
                    // 下拉单选
                    else if (dr["ctrltype"].ToString() == "3")
                    {
                        ComboBox text = new ComboBox();
                        text.QueryMode = Ext.Net.DataLoadMode.Local;
                        text.ID = "txt" + dr["fieldname"].ToString();
                        text.Name = dr["fielddisplay"].ToString();
                        text.FieldLabel = dr["fielddisplay"].ToString();
                        //text.Width = Convert.ToInt32(dr["fieldwidth"]);
                        text.Padding = 3;
                        text.LabelWidth = 80;
                        text.Text = dr["remark"].ToString();
                        text.ReadOnly = (dr["fieldreadonly"].ToString() == "True");
                        if (text.ReadOnly) text.ReadOnlyCls = "readonly";
                        //text.Format="yyyy-MM-dd";
                        pn.Items.Add(text);
                        text.DisplayField = "showname";
                        text.ValueField = "showid";
                        Store dt = new Store();
                        Model md = new Model();
                        ModelField field1 = new ModelField
                        {
                            Name = "showid"
                        };
                        md.Fields.Add(field1);
                        ModelField field2 = new ModelField
                        {
                            Name = "showname"
                        };
                        md.Fields.Add(field2);
                        dt.Model.Add(md);
                        DataSet da = new DataSet();
                        //店铺
                        if (dr["sourceid"].ToString() == "0")
                        {
                            da = db.DM_GetDateSet("select shopid as showid,shopname as showname from KM_v_shoplist");
                        }
                        //仓库
                        if (dr["sourceid"].ToString() == "1")
                        {
                            da = db.DM_GetDateSet("select shopid as showid,shopname as showname from KM_v_shoplist");
                        }
                        //年份
                        else if (dr["sourceid"].ToString() == "2")
                        {
                            da = db.DM_GetDateSet("select distinct yearno as showid,yearno as showname from km_v_mat order by yearno");
                        }
                        //季节
                        else if (dr["sourceid"].ToString() == "3")
                        {
                            da = db.DM_GetDateSet("select distinct seasonname as showid,seasonname as showname from km_v_mat");
                        }
                        //类型
                        else if (dr["sourceid"].ToString() == "4")
                        {
                            da = db.DM_GetDateSet("select distinct kindname as showid,kindname as showname from km_v_mat");
                        }
                        //系列
                        else if (dr["sourceid"].ToString() == "5")
                        {
                            da = db.DM_GetDateSet("select distinct seriesname as showid,seriesname as showname from km_v_mat");
                        }
                        //省份
                        else if (dr["sourceid"].ToString() == "6")
                        {
                            da = db.DM_GetDateSet("select distinct BigAreaName as showid,BigAreaName as showname from KM_v_shoplist");
                        }
                        //单据状态
                        else if (dr["sourceid"].ToString() == "9")
                        {
                            da = db.DM_GetDateSet("select StateId as showid,CNStateName as showname from KM_V_BillState");
                        }
                        if (da != null)
                        {
                            List<SourceInfo> sc = new List<SourceInfo>();
                            foreach (DataRow row in da.Tables[0].Rows)
                            {
                                SourceInfo aa = new SourceInfo
                                {
                                    showid = row["showid"].ToString(),
                                    showname = row["showname"].ToString()
                                };
                                sc.Add(aa);
                            }

                            dt.DataSource = sc;
                        }
                        text.Store.Add(dt);
                    }
                    // 下拉单选
                    else if (dr["ctrltype"].ToString() == "6")
                    {
                        MultiCombo text = new MultiCombo();
                        text.QueryMode = Ext.Net.DataLoadMode.Local;
                        text.ID = "txt" + dr["fieldname"].ToString();
                        text.Name = dr["fielddisplay"].ToString();
                        text.FieldLabel = dr["fielddisplay"].ToString();
                        //text.Width = Convert.ToInt32(dr["fieldwidth"]);
                        text.Padding = 3;
                        text.LabelWidth = 80;
                        text.Text = dr["remark"].ToString();
                        text.ReadOnly = (dr["fieldreadonly"].ToString() == "True");
                        if (text.ReadOnly) text.ReadOnlyCls = "readonly";
                        //text.Format="yyyy-MM-dd";
                        pn.Items.Add(text);
                        text.DisplayField = "showname";
                        text.ValueField = "showname";
                        Store dt = new Store();
                        Model md = new Model();
                        ModelField field1 = new ModelField
                        {
                            Name = "showname"
                        };
                        md.Fields.Add(field1);
                        dt.Model.Add(md);
                        DataSet da = new DataSet();
                        //店铺
                        if (dr["sourceid"].ToString() == "0")
                        {
                            da = db.DM_GetDateSet("select shopid as showid,shopname as showname from KM_v_shoplist");
                        }
                        //仓库
                        if (dr["sourceid"].ToString() == "1")
                        {
                            da = db.DM_GetDateSet("select shopid as showid,shopname as showname from KM_v_shoplist");
                        }
                        //年份
                        else if (dr["sourceid"].ToString() == "2")
                        {
                            da = db.DM_GetDateSet("select distinct yearno as showid,yearno as showname from km_v_mat order by yearno");
                        }
                        //季节
                        else if (dr["sourceid"].ToString() == "3")
                        {
                            da = db.DM_GetDateSet("select distinct seasonname as showid,seasonname as showname from km_v_mat");
                        }
                        //类型
                        else if (dr["sourceid"].ToString() == "4")
                        {
                            da = db.DM_GetDateSet("select distinct kindname as showid,kindname as showname from km_v_mat");
                        }
                        //系列
                        else if (dr["sourceid"].ToString() == "5")
                        {
                            da = db.DM_GetDateSet("select distinct seriesname as showid,seriesname as showname from km_v_mat");
                        }
                        //省份
                        else if (dr["sourceid"].ToString() == "6")
                        {
                            da = db.DM_GetDateSet("select distinct BigAreaName as showid,BigAreaName as showname from KM_v_shoplist");
                        }
                        //单据状态
                        else if (dr["sourceid"].ToString() == "9")
                        {
                            da = db.DM_GetDateSet("select StateId as showid,CNStateName as showname from KM_V_BillState");
                        }
                        if (da != null)
                        {
                            List<SourceInfo> sc = new List<SourceInfo>();
                            foreach (DataRow row in da.Tables[0].Rows)
                            {
                                SourceInfo aa = new SourceInfo
                                {
                                    showid = row["showid"].ToString(),
                                    showname = row["showname"].ToString()
                                };
                                sc.Add(aa);
                            }

                            dt.DataSource = sc;
                        }
                        text.Store.Add(dt);
                    }
                    // 选择框
                    else if (dr["ctrltype"].ToString() == "4")
                    {
                        Checkbox text = new Checkbox();
                        text.ID = "txt" + dr["fieldname"].ToString();
                        text.Name = dr["fielddisplay"].ToString();
                        text.BoxLabel = dr["fielddisplay"].ToString();
                        //text.Width = Convert.ToInt32(dr["fieldwidth"]);
                        text.Padding = 3;
                        text.LabelWidth = 80;
                        //text.Format="yyyy-MM-dd";
                        text.ReadOnly = (dr["fieldreadonly"].ToString() == "True");
                        if (text.ReadOnly) text.ReadOnlyCls = "readonly";
                        pn.Items.Add(text);
                    }
                    //lable 说明控件
                    else if (dr["ctrltype"].ToString() == "7")
                    {
                        Ext.Net.Label lb = new Ext.Net.Label();
                        lb.ID = "txt" + dr["fieldname"].ToString();
                        lb.Text = dr["remark"].ToString();
                        lb.Padding = 10;
                        lb.StyleSpec = "color:red;";
                        pn.Items.Add(lb);
                    }
                }
            }
        }
    }
    #endregion

    protected void RefreshDataSet(object sender, StoreReadDataEventArgs e)
    {
        BtnFind("");
    }
    protected void Store1_Submit(object sender, StoreSubmitDataEventArgs e)
    {
        //导出EXCEL
       ExtGridToExcel(mid, "xls", e.Xml, this.Response, this.Title);
    }
    protected void BtnExport(object sender, DirectEventArgs e)
    {
        if (Session["ResultData_" + mid.ToString()] != null)
        {
            if (!CheckModulesRights(mid, "M_Export", "您没有权限操作此功能，请联系管理员！", ""))
            {
                return;
            }
            InsActLog(mid, "导出");

            ResultDate = (DataSet)Session["ResultData_" + mid.ToString()];
            NpoiHelper.ExportByWeb(mid, ResultDate, this.Title);
        }
        else
        {
            ShowBox("无法导出数据！");
        }

    }
    /// <summary>
    /// 查询
    /// </summary>
    /// <param name="fields"></param>
    [DirectMethod(Namespace = "CompanyX")]
    public void BtnFind(string vals)
    {
        if(string.IsNullOrWhiteSpace(vals)){
            ShowBox("查询参数有问题！");
            return;
        }
        try{
            DataSet ds = GetQureyData(vals);
            DynaGridResult(ds);
        }catch(Exception ex){
            ShowBox("查询出错，错误："+ex.Message);
        }
    }
    [DirectMethod(Namespace = "CompanyX")]
    public void OutExcel(string vals)
    {
        if (string.IsNullOrWhiteSpace(vals))
        {
            ShowBox("查询参数有问题！");
            return;
        }
        try
        {
            DataSet ds = GetQureyData(vals);
            string path = NpoiHelper.ExportByWeb(mid, ds, this.Title);
            this.ResourceManager1.AddScript("outExcle('" + path + "')");
        }
        catch (Exception ex)
        {
            ShowBox("查询出错，错误：" + ex.Message);
        }
    }
    /*
    #region 计算过虑条件，执行命令。
    [DirectMethod(Namespace = "CompanyX")]
    public void BtnFind(string fields,string vals)
    {
        //if (HD_Fiels.Value.ToString() == "")
        //{
        //    ShowBox("请选择输出项目！");
        //    return;
        //}
        string dynaSumFilds = "";
        string dynaFilds = "";

        if (this.HD_Fiels.Value.ToString() != "")
        {
            string[] fld = HD_Fiels.Value.ToString().Split(',');
            foreach (string s in fld)
            {
                if (s != "")
                {
                    if (s.ToLower().IndexOf("amount") > 0 || s.IndexOf("qty") > 0)
                    {
                        dynaSumFilds += "sum(" + s + "),";
                    }
                    else
                    {
                        dynaFilds += s + ",";
                    }
                }
            }
        }
        string sql = "";
        string tablename="";
        string mtype = "";
        DataSet ds1 = mydb.DM_GetDateSet("select mtype,moduletab from sys_modules where parentid=" 
            + mid + " and modulename='网格'");
        if (ds1 != null)
        {
            tablename = ds1.Tables[0].Rows[0]["moduletab"].ToString();
            mtype = ds1.Tables[0].Rows[0]["mtype"].ToString();
            sql += tablename + " ";
            dynaSumFilds = dynaSumFilds.TrimEnd(',');
            string[] param = vals.Split('#');
            var str = "";
            foreach (string p in param)
            {
                //if (p.IndexOf(',') != -1)
                //{
                //    str = p;
                //    //str = StrSplit(str);
                //    str = "(" + p + ")";
                //    sql += "'" + str + "',";
                //}
                //else
                sql += "'" + p + "',";
            }
            //sql += " '" + dynaFilds + dynaSumFilds + "','" + dynaFilds.TrimEnd(',') + "'";

            sql = sql.TrimEnd(',');
            //ShowBox(sql);
            //StoreColumns(Store1, mid, "GridPanel1");
            //DataSet ds = null;
            try
            {
                if (mtype == "0")
                {
                    ResultDate = mydb.DM_GetDateSet(sql);
                }
                else if (mtype == "1")
                {
                    ResultDate = db.DM_GetDateSet(sql);
                }
                else {
                    ResultDate = hkdb.DM_GetDateSet(sql);
                }
                Session["ResultData_"+mid.ToString()] = ResultDate;
            }
            catch (Exception ex)
            {
                ShowBox("执行结果出现问题！");
            }
            if (ResultDate != null)
            {
                DynaGridResult(ResultDate);
            }
            else
            {
                ShowBox("执行结果出现问题！");
            }
        }
        else
        {
            ShowBox("报表配制有错误，请检查！");
        }
        
    }
    #endregion
     */

    /// <summary>
    /// 前台传入参数，合并SQL，获取执行结果
    /// </summary>
    /// <param name="ParamStr"></param>
    /// <returns></returns>
    private DataSet GetQureyData(string ParamStr)
    {
        string sql = "";
        string tablename = "";
        string mtype = "";
        DataSet ds1 = mydb.DM_GetDateSet("select mtype,moduletab,dataRight from sys_modules where parentid="
            + mid + " and modulename='网格'");
        DataSet result = null;
        if (ds1 != null)
        {
            tablename = ds1.Tables[0].Rows[0]["moduletab"].ToString();
            mtype = ds1.Tables[0].Rows[0]["mtype"].ToString();
            var DataRight = (bool)ds1.Tables[0].Rows[0]["dataRight"];
            sql += tablename + " ";
            string[] param = ParamStr.Split('#');
            foreach (string p in param)
            {
                sql += "'" + p + "',";
            }
            sql = sql.TrimEnd(',');
            //条件加入用户ID
            if (DataRight)
                sql += "," + this.UserId;
            try
            {
                if (mtype == "0")
                    result = mydb.DM_GetDateSet(sql);
                else if (mtype == "1")
                    result = db.DM_GetDateSet(sql);
                else
                    result = hkdb.DM_GetDateSet(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        return result;
    }
    
    #region 动态加载报表显示结果
    private void DynaGridResult(DataSet ds)
    {
        if (ds == null) return;

        Store store = this.Store1;
        GridPanel grid = this.GridPanel1;

        // Clear Collections to remove old Data and Models
        store.Reader.Clear();
        //grid.SelectionModel.Clear();
        grid.ColumnModel.Columns.Clear();
        store.Model.Clear();
        //store.Model.Add(new Model
        //{
        //    Fields = 
        //    {
        //        new ModelField("ShopName"),
        //        new ModelField("CashAmt", ModelFieldType.Float),
        //    }
        //});
        

        //grid.SelectionModel.Add(new RowSelectionModel { Mode = SelectionMode.Single });
        //grid.ColumnModel.Columns.Add(new ColumnBase[] { 
        //    new Column 
        //    {
        //        Text = "ShopName",
        //        DataIndex = "ShopName",
        //        Flex = 1
        //    },
        //    new Column
        //    {
        //        Text = "CashAmt",
        //        DataIndex = "CashAmt",
        //        Renderer = { Format = RendererFormat.UsMoney }
        //    }
        //});

        string sql = @"select mu_id,lindx,fieldname,fielddisplay,mu_visible,fieldwidth,datatype,gridfoot,showtype,fieldreadonly,isctrl,
                    datatype,showtype,ctrltype,ctrl_x,ctrl_y,iskeyid,ismust
                    from sys_moduleslayout a 
                    inner join sys_modulesuserlayout b on b.fid =lid 
                    inner join sys_modules m on m.mid = a.mid 
                    where mu_visible = 1 and m.parentid = " + mid + " and m.moduletype=2 "
                 + " and userid=" + Session["UserID"] + " order by lindx";

        DataSet ds2 = mydb.DM_GetDateSet(sql);
        Model md = new Model();
        List<ColumnBase> list = new List<ColumnBase>();

        DataTable dt = ds2.Tables[0];
        string displayname = "";
        //for (int i = 0; i < dt.Columns.Count; i++)
        foreach (DataRow dr in ds2.Tables[0].Rows)
        {
            //根据结果集创建Store
//            try
//            {
//                displayname = mydb.DM_GetExecuteScalarObj(@"select isnull(FieldDisplay,'') from [dbo].[Sys_ModulesLayout]a 
//                    inner join sys_modules m on m.mid = a.mid 
//                    where m.parentid =" + mid + " and FieldName = '" + dt.Columns[i].ToString() + "' and moduletype = 2").ToString();
//            }
//            catch { }

            ModelField field = new ModelField();
            field.Name = dr["fieldname"].ToString();
            field.Mapping = dr["fielddisplay"].ToString();
            md.Fields.Add(field);
            //创建grid colums
            Column cl = new Column();
            cl.Text = dr["fielddisplay"].ToString();
            cl.DataIndex = dr["fieldname"].ToString();
            cl.Width = Convert.ToInt32(dr["fieldwidth"]);
            //合计
            if (dr["gridfoot"].ToString() == "1")
            {
                cl.SummaryType = SummaryType.Sum;
                if (dr["showtype"].ToString() == "1")//单价
                    cl.Renderer.Fn = "price";
                else if (dr["showtype"].ToString() == "2")//金额
                    cl.Renderer.Fn = "paymoney";
                else if (dr["showtype"].ToString() == "3")//数量
                    cl.Renderer.Fn = "shuliang";
                else if (dr["showtype"].ToString() == "4")//日期
                    cl.Renderer.Fn = "date";
                else if (dr["showtype"].ToString() == "5")//百分比
                    cl.Renderer.Fn = "pctChange";
            }
            list.Add(cl);
        }
        store.Model.Add(md);
        store.DataSource = ds;
        store.DataBind();
        grid.ColumnModel.Columns.Add(list);
        grid.Render();
    }
    #endregion
    #region 输出项项目加载
    protected void ShowFiledWindows()
    {
        //加载报表输出项
        string sql = @"select mu_id,lindx,fieldname,fielddisplay,mu_visible,fieldwidth,datatype,gridfoot,showtype,fieldreadonly,isctrl,
                    datatype,showtype,ctrltype,ctrl_x,ctrl_y,iskeyid,ismust
                    from sys_moduleslayout a 
                    inner join sys_modulesuserlayout b on b.fid =lid 
                    inner join sys_modules m on m.mid = a.mid 
                    where m.parentid = " + mid + " and m.modulecode='GridPanel1'"
                 + " and userid=" + Session["UserID"] + " order by lindx";
        DataSet ds = mydb.DM_GetDateSet(sql);

        //Node reprotNode = new Node();
        //reprotNode.Text = "报表输出项";
        //reprotNode.Icon = Ext.Net.Icon.Folder;
        foreach(DataRow dr in ds.Tables[0].Rows)
        {
            Node childNode = new Node();
            childNode.NodeID = dr["fieldname"].ToString();
            childNode.Text = dr["fielddisplay"].ToString();
            childNode.Leaf = true;
            childNode.Checked = false;
            TreePanel1.Root[0].Children[0].Children.Add(childNode);
        }
        //this.TreePanel1.Root.Add(reprotNode);
        //加载基础资料
        //this.Window1.Show();
        sql = "select * from sys_modules where parentid = " + mid+" and moduletype = 4";
        ds = mydb.DM_GetDateSet(sql);
        DataSet ds2;
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            Node mNode = new Node();
            mNode.NodeID = dr["mid"].ToString();
            mNode.Text = dr["modulename"].ToString();
            

            sql = @"select mu_id,lindx,fieldname,fielddisplay,mu_visible,fieldwidth,datatype,gridfoot,showtype,fieldreadonly,isctrl,
                    datatype,showtype,ctrltype,ctrl_x,ctrl_y,iskeyid,ismust
                    from sys_moduleslayout a 
                    inner join sys_modulesuserlayout b on b.fid =lid 
                    inner join sys_modules m on m.mid = a.mid 
                    where m.parentid = " + mid + " and m.modulecode='" + dr["modulecode"].ToString() + "'"
                 + " and userid=" + Session["UserID"] + " order by lindx";

            ds2 = mydb.DM_GetDateSet(sql);
            //Node reprotNode = new Node();
            //reprotNode.Text = "报表输出项";
            //reprotNode.Icon = Ext.Net.Icon.Folder;
            foreach (DataRow dr2 in ds2.Tables[0].Rows)
            {
                Node childNode = new Node();
                childNode.NodeID = dr2["fieldname"].ToString();
                childNode.Text = dr2["fielddisplay"].ToString();
                childNode.Leaf = true;
                childNode.Checked = false;
                mNode.Children.Add(childNode);
            }
            TreePanel1.Root[0].Children.Add(mNode);
        }
    }
    #endregion

}

public class SourceInfo
{
    public string showid { get; set; }
    public string showname { get; set; }
}